package innreservations;
//import com.sun.xml.internal.ws.api.pipe.NextAction;
import java.sql.*;
import java.util.*;
import java.io.*;
import java.lang.*;

/**
 *
 * @author Ethan Frame, Grant Frame, Timothy Phan
 */
public class InnReservations {

    /**
     * @param args the command line arguments
     */
    public static Connection conn;
    public static int rooms_count = 0, reservations_count = 0;
    public static boolean empty_database = false;
    private static InnReservationUI gui;
    
    public static void main(String[] args) throws FileNotFoundException, IOException {
        //setup database
        String url, login, password;
        FileReader fr = new FileReader ("ServerSettings.txt");
        BufferedReader br = new BufferedReader (fr);

        url = br.readLine();
        login = br.readLine();
        password = br.readLine();

        try {
            Class.forName("oracle.jdbc.OracleDriver");
        }
        catch (Exception ex) {
            System.out.println("Driver not found");
        }

        conn = null;
        try {
            conn = DriverManager.getConnection(url, login, password);
        }
        catch (Exception ex) {
            System.out.println("Could not open connection");
        }
        System.out.println("Connected");

        createTable("ROOMS");
        createTable("RESERVATIONS");
        
        // run gui
        gui = new InnReservationUI();
        gui.setVisible(true);
        
        //get inital number of tuples in each table
        rooms_count = checkTable("ROOMS");
        reservations_count = checkTable("RESERVATIONS");
        
        if(reservations_count >= 600) {
            InnReservationUI.res_code = get_start_res_code();
        }
        
        //set gui statuses
        gui.setDatabaseStatus(rooms_count, reservations_count, empty_database);
        gui.setReservationsStatus(reservations_count, empty_database);
        gui.setRoomsStatus(rooms_count, empty_database);    
    }
    
    public static void update_status() {
        gui.setDatabaseStatus(rooms_count, reservations_count, false);
        gui.setReservationsStatus(reservations_count, false);
        gui.setRoomsStatus(rooms_count, false);
    }

    public static void createTable(String str) {
        String sql = null;
        try {
            Statement s1 = conn.createStatement();
            String tableCheck = "select table_name from user_tables where "
                    + "table_name = '" + str + "'";
            ResultSet result = s1.executeQuery(tableCheck);
            int count = 0;
            while(result.next()) {
                String s = result.getString(1);
                System.out.println(s);
                count++;
            }
            if (count == 0) {
                if (str.equals("ROOMS")) {
                    sql = "CREATE TABLE ROOMS" +
                                 "(Room_Id Varchar2(3) Primary Key, " +
                                 "Room_Name Varchar2(30), "
                                 + "Beds int, "
                                 + "Bed_Type Varchar2(10), "
                                 + "Max_Occupancy int, "
                                 + "Base_Price int, "
                                 + "Decor Varchar2(25))";
                }
                else if (str.equals("RESERVATIONS")) {
                    sql = "CREATE TABLE RESERVATIONS"
                            + "(Code int Primary Key, "
                            + "Room Varchar2(3) References rooms, "
                            + "Check_In Date, "
                            + "Check_Out Date, "
                            + "Rate Float, "
                            + "Last_Name Varchar2(25), "
                            + "First_Name Varchar2(25), "
                            + "Adults int Check(Adults > 0), "
                            + "Kids int)";
                }
                s1.executeUpdate(sql);
            }
        }
        catch (Exception ee) {
            System.out.println(ee);
        }
    }
    
    public static int checkTable(String str) {
        
        try {
            Statement s1 = conn.createStatement();
            String tableCheck = "select count(*) from " + str;
            ResultSet result = s1.executeQuery(tableCheck);
            
            while(result.next()) {
                return result.getInt(1);
            }
        }
        catch (Exception ee) {
            System.out.println(ee);
        }
        
        return 0;
    }
    
    public static String format_date(String date) {
        String formated_date = "";
        String[] values = date.split(",");
        
        if(values[0].equals("January")) {
            formated_date = values[1].trim() + "-JAN-2010";
        } else if(values[0].equals("February")) {
            formated_date = values[1].trim() + "-FEB-2010";
        } else if(values[0].equals("March")) {
            formated_date = values[1].trim() + "-MAR-2010";
        } else if(values[0].equals("April")) {
            formated_date = values[1].trim() + "-APR-2010";
        } else if(values[0].equals("May")) {
            formated_date = values[1].trim() + "-MAY-2010";
        } else if(values[0].equals("June")) {
            formated_date = values[1].trim() + "-JUN-2010";
        } else if(values[0].equals("July")) {
            formated_date = values[1].trim() + "-JUL-2010";
        } else if(values[0].equals("August")) {
            formated_date = values[1].trim() + "-AUG-2010";
        } else if(values[0].equals("September")) {
            formated_date = values[1].trim() + "-SEP-2010";
        } else if(values[0].equals("October")) {
            formated_date = values[1].trim() + "-OCT-2010";
        } else if(values[0].equals("November")) {
            formated_date = values[1].trim() + "-NOV-2010";
        } else if(values[0].equals("December")) {
            formated_date = values[1].trim() + "-DEC-2010";
        }
        
        return formated_date;
    }
    
    public static String get_room_info(String room, boolean owner) {
        String room_info = "";
        
        try {
            Statement s = conn.createStatement();
            ResultSet result = s.executeQuery("select * from rooms where (room_name = '" + room + "')");
            result.next();
            room_info += "Room Code: " + result.getString(1) +
                            "\nRoom Name: " + result.getString(2) +
                            "\nBeds: " + result.getInt(3) +
                            "\nBed Type: " + result.getString(4) +
                            "\nMax Occupancy: " + result.getString(5) +
                            "\nBase Price: " + result.getString(6) +
                            "\nDecor: " + result.getString(7);
            if(owner) {
                result = s.executeQuery("select sum(Check_Out - Check_In) as Nights_Occupied, " +
                                    "((sum(Check_Out - Check_In) / 365) * 100) as Percent, " +
                                    "sum((Check_Out - Check_In) * Rate) as Revenue " +
                                    "from rooms, reservations " +
                                    "where (room_name = '" + room +"') and " +
                                    "(room = room_id)");
            result.next();
            int nights = result.getInt(1);
            double percent_days = result.getDouble(2);
            double revenue = result.getDouble(3);
            
            result = s.executeQuery("select sum((check_out - check_in) * rate) as Total_Revenue " +
                                    "from reservations");
            
            result.next();
            double percent_revenue = (revenue / result.getDouble(1)) * 100.0;
            
            room_info += "\nNights Occupied: " + nights +
                        "\nPercent Occupied: " + percent_days +
                        "\nTotal Revenue: " + revenue +
                        "\nPercent Revenue: " + percent_revenue;
            }
            
        } catch(Exception e) {
            System.out.println(e.toString());
        }
        
        return room_info;
    }
    
    public static String[] reservations_for_room(String room) {
        ArrayList<String> values = new ArrayList<String>();
        
        try {
            Statement s = conn.createStatement();
            ResultSet result = s.executeQuery("select Code, room, check_in, check_out " +
                                            "from rooms, reservations " +
                                            "where (room_name = '" + room + "') and " +
                                                    "(room = room_id) " +
                                            "order by check_in, check_out");
            for(int i=0; result.next(); i++) {
                values.add(i, result.getString(1) + ", " + result.getString(2) + ", " +
                        result.getDate(3) + ", " + result.getDate(4));
            }
            
        } catch (Exception e) {
            //System.out.println(e.toString());
        }
        
        return values.toArray(new String[5]);
    }
    
    public static String get_reservation_info(String reservation_code) {
        String reservation_info = "";
        String[] reservation_code_delimited;
            
        
        try {
            reservation_code_delimited = reservation_code.split(",");
            Statement s = conn.createStatement();
            ResultSet result = s.executeQuery("select r.Room_Name, re.* " +
                                            "from rooms r, reservations re " +
                                            "where (re.Code = '" + reservation_code_delimited[0] + "') and " +
                                                    "(re.Room = r.Room_Id)");
            result.next();
            reservation_info = "Room Name: " + result.getString(1) +
                                "\nCode: " + result.getString(2) +
                                "\nRoom Code: " + result.getString(3) +
                                "\nCheck In: " + result.getDate(4).toString() +
                                "\nCheck Out: " + result.getDate(5).toString() +
                                "\nRate: " + result.getString(6) +
                                "\nLast Name: " + result.getString(7) +
                                "\nFirst Name: " + result.getString(8) +
                                "\nAdults: " + result.getString(9) +
                                "\nKids: " + result.getString(10);

        } catch(Exception e) {}
        
        return reservation_info;
    }
    
    public static String[][] check_availability(String room, String start_date, String end_date) {
        ArrayList<String> days = new ArrayList<String>();
        ArrayList<String> occupied = new ArrayList<String>();
        ArrayList<Double> rates = new ArrayList<Double>();
        String[][] values = new String[3][365];
        boolean weekend_found = false;
        boolean special_day_found = false;
        int num_days = 0;
        
        try {
            Statement s = conn.createStatement();
            ResultSet result = s.executeQuery("select distinct d1 - d2 as d3 " +
                                                "from " +
                                                "(select room_id, to_date('" + end_date + "', 'DD-MON-YYYY') as D1 " +
                                                "   from rooms " +
                                                ") r1, " +
                                                "(select room_id, to_date('" + start_date + "', 'DD-MON-YYYY') as D2 " +
                                                "   from rooms " +
                                                ") r2 " +
                                                "where (r1.room_id = r2.room_id)");
            result.next();
            num_days = result.getInt(1);
            
            for(int i=0; i<num_days; i++) {
                result = s.executeQuery("select count(*) " +
                                            "from reservations, rooms " +
                                            "where (room_name = '" + room + "') and " +
                                            "      (check_in <= To_Date('" + start_date + "', 'DD-MON-YYYY') + INTERVAL '" + i + "' DAY(4) and " +
                                            "      check_out >= To_Date('" + start_date + "', 'DD-MON-YYYY') + INTERVAL '" + (i+1) + "' DAY(4)) and " +
                                            "      (room = room_id)");
                result.next();
                int count = result.getInt(1);
                if(count == 0) {
                    occupied.add(i, "empty");
                    result = s.executeQuery("select base_price " +
                                                "from rooms " +
                                                "where (room_name = '" + room + "')");
                    result.next();
                    rates.add(i, result.getDouble(1));
                } else {
                    occupied.add(i, "occupied");
                    rates.add(i, 0.0);
                }
                
                result = s.executeQuery("select (To_Date('" + start_date + "', 'DD-MON-YYYY') + INTERVAL '" + i + "' DAY(4)) as D " +
                                            "from dual");
                result.next();
                days.add(i, result.getDate(1).toString());
                
                result = s.executeQuery("select To_Char((To_Date('" + start_date + "', 'DD-MON-YYYY') + INTERVAL '" + i + "' DAY(4)), 'DY') as D, "
                        + "to_char((To_Date('" + start_date + "', 'DD-MON-YYYY') + INTERVAL '" + i + "' DAY(4)), 'MON-DD') as D2 " +
                                            "from dual");
                result.next();
                String day = result.getString(1);
                String date = result.getString(2);
                if(day.equals("SAT") || day.equals("SUN")) {
                    weekend_found = true;
                }
                if(date.equals("JAN-01") || date.equals("JUL-04") || date.equals("SEP-06") || date.equals("OCT-30")) {
                    special_day_found = true;
                }
            }
        } catch (Exception e) {
            System.out.println(e.toString());
        }
        
        if(special_day_found) {
            for(int i = 0; i < rates.size(); i++) {
                values[2][i] = rates.get(i) * 1.25 + "";
            }
        }
        else if(weekend_found) {
            for(int i=0; i<rates.size(); i++) {
                values[2][i] = rates.get(i) * 1.1 + "";
            }
        } else {
            for(int i=0; i<num_days; i++) {
                values[2][i] = rates.get(i) + "";
            }
        }
        
        values[0] = days.toArray(new String[1]);
        values[1] = occupied.toArray(new String[1]);
        
        return values;
    }
    
    public static String[][] get_available_rooms(String start_date, String end_date) {
        String[][] values = new String[2][10];
        int num_days;
        boolean weekend_found = false;
        boolean special_day_found = false;
        
        try {
            Statement s = conn.createStatement();
            ResultSet result = s.executeQuery("(select room_name, base_price " +
                                                "from rooms) " +
                                                "MINUS " +
                                                "(select distinct room_name, base_price " +
                                                "from reservations, rooms " +
                                                "where (check_in < '" + end_date + "' and " +
                                                "	check_out > '" + start_date + "') and " +
                                                "	(room = room_id) " +
                                                ")");
            for(int i=0; result.next(); i++) {
                values[0][i] = result.getString(1);
                values[1][i] = result.getString(2);
            }
            
            result = s.executeQuery("select distinct d1 - d2 as d3 " +
                                        "from " +
                                        "(select room_id, to_date('" + end_date + "', 'DD-MON-YYYY') as D1 " +
                                        "   from rooms " +
                                        ") r1, " +
                                        "(select room_id, to_date('" + start_date + "', 'DD-MON-YYYY') as D2 " +
                                        "   from rooms " +
                                        ") r2 " +
                                        "where (r1.room_id = r2.room_id)");
            result.next();
            num_days = result.getInt(1);
            
            for(int i=0; i<num_days; i++) {
                result = s.executeQuery("select To_Char((To_Date('" + start_date + "', 'DD-MON-YYYY') + INTERVAL '" + i + "' DAY(4)), 'DY') as D, " +
                                            "to_char((To_Date('" + start_date + "', 'DD-MON-YYYY') + INTERVAL '" + i + "' DAY(4)), 'MON-DD') as D2 " +
                                            "from dual");
                result.next();
                String day = result.getString(1);
                String date = result.getString(2);
                
                if(day.equals("SAT") || day.equals("SUN")) {
                    weekend_found = true;
                }
                if(date.equals("JAN-01") || date.equals("JUL-04") || date.equals("SEP-06") || date.equals("OCT-30")) {
                    special_day_found = true;
                }
            }
            
            if(special_day_found) {
                for(int i = 0; i < values[1].length; i++) {
                    values[1][i] = String.valueOf(Double.valueOf(values[1][i]) * 1.25);
                }
            }
            else if(weekend_found) {
                for(int i=0; i<values[1].length; i++) {
                    values[1][i] = String.valueOf(Double.valueOf(values[1][i]) * 1.1);
                }
            }
            
        } catch(Exception e) {
            ;
        }
        
        return values;
    }
    
    public static int get_max_occupancy(String room_name) {
        
        try {
            Statement s = conn.createStatement();
            ResultSet result = s.executeQuery("select max_occupancy " +
                                                "from rooms " +
                                                "where (room_name = '" + room_name + "')");
            
            result.next();
            return result.getInt(1);
            
        } catch (Exception e) {
            ;
        }
        
        return 0;
    }
    
    public static void create_reservation(String code, String room, String check_in, String check_out,
            String rate, String last_name, String first_name, String adults, String kids) {
        
        try {
            PreparedStatement ps = conn.prepareStatement("insert into reservations values(?,?,?,?,?,?,?,?,?)");
            
            ps.setInt(1, Integer.parseInt(code));
            
            Statement s = conn.createStatement();
            ResultSet result = s.executeQuery("select room_id " +
                                                "from rooms " +
                                                "where (room_name = '" + room + "')");
            result.next();
            ps.setString(2, result.getString(1));
            ps.setString(3, check_in);
            ps.setString(4, check_out);
            ps.setFloat(5, Float.parseFloat(rate));
            ps.setString(6, last_name);
            ps.setString(7, first_name);
            ps.setInt(8, Integer.parseInt(adults));
            ps.setInt(9, Integer.parseInt(kids));
            
            ps.executeUpdate();
            
        } catch (Exception e) {
            System.out.println(e.toString());
        }
    }
    
    public static int get_start_res_code()
    {
        try {
            Statement s = conn.createStatement();
            ResultSet result = s.executeQuery("select max(code) " +
                                                "from reservations");
            
            result.next();
            int max_res_code = result.getInt(1);
            if(max_res_code < 100000) {
                return 100000;
            } else {
                return max_res_code + 1;
            }
        } catch (Exception e) {
            ;
        }
        
        return 0;
    }
}
